package database;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.Scanner;
import java.util.TimeZone;

import utility.Constants;

public final class Database{

	private final static String groupId = "g_id";
	private final static String group = "groups";
	private final static String activityName = "name";
	private final static String maintenance = "maintenance";
	//private final static int MAINTANCE_ACTIVITY_ID = SqlWrapper.index;
	private final static Column activityNameColumn = new Column(true, activityName, SqlWrapper.TEXT);
	private final static Table groupTable = new Table(group, activityNameColumn);

	private final static String activities = "activities";
	//private final static String total = "total";
	private final static String startDate = "start_date";
	private final static String endDate = "end_date";
	private final static String ALL = "all";

	final static Column groupIdColumn = new Column(groupId, groupTable, SqlWrapper.ROWID);

	private final static Column[] activitiesColumns = {activityNameColumn, groupIdColumn}; 
	private static Table activitiesTable = new Table(activities, activitiesColumns);

	private final static String comment = "comment";
	private final static String postId = "p_id";	
	private final static String activity_id = "a_id";

	private final static Column commentColumn = new Column(comment, SqlWrapper.TEXT, null);	
	public final static Column postIdColumn = new Column(true,postId, SqlWrapper.INT);
	public final static Column sessionStartTimeColumn =  new Column(true, startDate, SqlWrapper.DATE);
	final static Column sessionEndTimeColumn = new Column(true,endDate, SqlWrapper.DATE);
	final static Column activityIdColumn = new Column(activity_id, activitiesTable, SqlWrapper.ROWID);

	private final static String recordedSessions = "recordedsessions";

	private final static Column[] recordedSessionsColumns = {sessionStartTimeColumn,sessionEndTimeColumn,postIdColumn,commentColumn, activityIdColumn};
	public final static Table recordedSessionsTable = new Table(recordedSessions, recordedSessionsColumns);

	public static void init(){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			con.setAutoCommit(false);

			final Statement st = con.createStatement();
			st.execute(SqlWrapper.create(groupTable));
			st.executeUpdate(SqlWrapper.insert(groupTable,ALL));
			st.execute(SqlWrapper.create(activitiesTable));
			st.executeUpdate(SqlWrapper.insert(activitiesTable, maintenance));
			st.execute(SqlWrapper.create(recordedSessionsTable));

			con.commit();
			con.setAutoCommit(true);
			st.close();
			con.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}	
	}

	public static void modifyEndTimeOf(final int activityId, final long longEndTime){
		try {
			final Timestamp newEndTime = new Timestamp(longEndTime);
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();

			ResultSet rs = st.executeQuery(SqlWrapper.last(recordedSessionsTable, SqlWrapper.ROWID,activityIdColumn,activityId));
			final int rowId = rs.getInt(1);
			st.executeUpdate(SqlWrapper.update(recordedSessionsTable, sessionEndTimeColumn, new Timestamp(getLocalMillis(newEndTime)), SqlWrapper.ROWID, rowId));

			//	rs.close();
			//	rs = st.executeQuery(SqlWrapper.select(recordedSessionsTable, sessionStartTimeColumn, SqlWrapper.ROWID, rowId));
			//	final Object ret = rs.getObject(1);
			//	final Timestamp startDate = Timestamp.valueOf(ret.toString());
			//	if (!newEndTime.after(startDate)) throw new RuntimeException();

			//	final Connection con = st.getConnection();
			//	con.setAutoCommit(false);

			//	st.executeUpdate(SqlWrapper.update(activitiesTable, totalColumn,
			//		(int) (newEndTime.getTime() - startDate.getTime()) / Constants.MM_MILLIS, SqlWrapper.ROWID, activityId));

			//	con.commit();
			//	con.setAutoCommit(true);
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void modifyEndTimeOf(final int activityId){
		try {
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();

			ResultSet rs = st.executeQuery(SqlWrapper.last(recordedSessionsTable, SqlWrapper.ROWID,activityIdColumn,activityId));
			final int rowId = rs.getInt(1);
			st.executeUpdate(SqlWrapper.update(recordedSessionsTable, sessionEndTimeColumn, SqlWrapper.TIMESTAMP, SqlWrapper.ROWID, rowId));
			con.close();
		}catch(SQLException e){
			if (e.getMessage().contains("not unique")){
				try {
					Thread.sleep(1000);
					modifyEndTimeOf(activityId);
				} catch (InterruptedException e1) {
					e1.printStackTrace();
				}
			}
		}catch (Exception e1){
			e1.printStackTrace();
		}
	}

	public static void saveComment(final String comment, final int activityId){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(recordedSessionsTable, SqlWrapper.ROWID,activityIdColumn,activityId, SqlWrapper.DESC));
			rs.next();
			st.executeUpdate(SqlWrapper.update(recordedSessionsTable, commentColumn, comment, SqlWrapper.ROWID, rs.getInt(SqlWrapper.ROWID.getName())));
			con.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	/*
	public static int getTotal(final String activityName){
		try{
			final Statement st = SqlWrapper.connectToSQLite();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, totalColumn, activityNameColumn, activityName));
			rs.next();
			final int ret = rs.getInt(totalColumn.getName());
			st.close();
			st.getConnection().close();
			return ret;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static int getTotal(final int activityId){
	/*	try{
			final Statement st = SqlWrapper.connectToSQLite();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, totalColumn, activityIdColumn, activityId));
			final int ret = rs.getInt(totalColumn.getName());
			st.close();
			st.getConnection().close();
			return ret;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}*/
	/*
	public int getGroupTotal(final String groupName){
		try{final Statement st = SqlWrapper.connectToSQLite();
		ResultSet rs = st.executeQuery(SqlWrapper.select(groupTable, SqlWrapper.ROWID, activityNameColumn,groupName)); 
		if (! rs.next()) throw new RuntimeException("no entry found for "+groupName);
		final int groupId = rs.getInt(SqlWrapper.ROWID.getName());
		st.close();
		st.getConnection().close();
		return getGroupTotal(groupId);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static int getGroupTotal(final int groupId){
		int total = 0;
		try{
			final Statement st = SqlWrapper.connectToSQLite();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, totalColumn,groupIdColumn,groupId));

			while (rs.next()) total += rs.getInt(totalColumn.getName());
			st.close();
			st.getConnection().close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return total;
	}
	 */
	private static boolean scan(final Timestamp a, final Timestamp b, final int activityId, final Statement st){
		final Column[] cols = {sessionStartTimeColumn, sessionEndTimeColumn, activityIdColumn};
		final String[] values1 = {a.toString(),b.toString(), Integer.toString(activityId)};
		try{
			st.executeUpdate(SqlWrapper.insert(recordedSessionsTable,cols,values1));
		}catch(SQLException e){
			return false;			
		}
		return true;
	}

	public static void importRecords(final File f, final int max){
		Statement st = null;
		Connection con = null;
		try{
			con = SqlWrapper.connectToSQLite();
			con.setAutoCommit(false);
			st = con.createStatement();
		}catch(Exception e){
			e.printStackTrace();
		}
		Scanner sc = null;
		try {
			sc = new Scanner(f);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		int i=0;
		while (sc.hasNext() && i++ < max ){
			final String s = sc.nextLine();
			final int comma = s.indexOf(',');
			final int activityId = Integer.parseInt(s.substring(0,comma));
			final int comma1 = s.indexOf(',',comma+1);
			long startTime = Long.parseLong(s.substring(comma+1,comma1));
			Timestamp start = new Timestamp(startTime);
			long endTime = Long.parseLong(s.substring(comma1+1));
			Timestamp end = new Timestamp(endTime);

			scan(start,end,activityId,st);/*
			while (! scan(start,end,activityId,st)){
				startTime += Constants.SS_MILLIS;
				endTime += Constants.SS_MILLIS;
				start = new Timestamp(startTime);
				end = new Timestamp(endTime);
			}*/
		}
		try {
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void addRecordedSession(final int activityId){
		try{
			final String[] values = {SqlWrapper.TIMESTAMP, SqlWrapper.TIMESTAMP, Integer.toString(activityId)};
			final Column[] columns = {sessionStartTimeColumn, sessionEndTimeColumn, activityIdColumn};
			final Connection con = SqlWrapper.connectToSQLite();
			//	con.setAutoCommit(false);

			final Statement st = con.createStatement();
			st.executeUpdate(SqlWrapper.insert(recordedSessionsTable,columns,values));

			//	con.commit();
			//	con.setAutoCommit(true);
			con.close();
		}
		catch(SQLException e){
			if (e.getMessage().contains("not unique")){
				try {
					Thread.sleep(1000);
					addRecordedSession(activityId);
				} catch (InterruptedException e1) {
					e1.printStackTrace();
				}
			}
		}catch (Exception e1){
			e1.printStackTrace();
		}
	}

	private static String gmtString(final long startTime){
		final Date d = new Date(startTime);
		final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
		return sdf.format(d);
	}

	public static void addRecordedSession(final int activityId, long startTime){

		try{
			final String[] values = {gmtString(startTime), SqlWrapper.TIMESTAMP, Integer.toString(activityId)};
			final Column[] columns = {sessionStartTimeColumn, sessionEndTimeColumn, activityIdColumn};
			final Connection con = SqlWrapper.connectToSQLite();

			final Statement st = con.createStatement();
			st.executeUpdate(SqlWrapper.insert(recordedSessionsTable,columns,values));

			con.close();
		}
		catch(SQLException e){
			if (e.getMessage().contains("not unique")){
				startTime += Constants.SS_MILLIS;
				addRecordedSession(activityId, startTime);
			}
		} catch (Exception e1){
			e1.printStackTrace();
		}
	}


	public int getLatestPostId(){
		try{
			final Connection con = SqlWrapper.connectToSQLite();

			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.max(recordedSessionsTable, postIdColumn));
			rs.next();
			final int ret = rs.getInt(postIdColumn.getName());
			st.close();
			st.getConnection().close();
			return ret;
		}
		catch (Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	private static long getLocalMillis(final Timestamp gmt){
		final GregorianCalendar gc = new GregorianCalendar();
		int offset = gc.get(Calendar.ZONE_OFFSET);
		long time = gmt.getTime() + offset;
		return time;
	}

	private static Timestamp getLastDate(final int activityId, final boolean last, final Statement st){
		try{
			final Column wanted;
			if(last) wanted = sessionEndTimeColumn;
			else wanted = sessionStartTimeColumn;
			final ResultSet rs = st.executeQuery(SqlWrapper.last(recordedSessionsTable, wanted,activityIdColumn,activityId));
			Timestamp ret =  Timestamp.valueOf(rs.getObject(wanted.getName()).toString());
			return new Timestamp(getLocalMillis(ret));
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static Timestamp getLastFirstDate(final int activityId){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final Timestamp ret = getLastDate(activityId,false,st);
			con.close();
			return ret;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static Timestamp getLastEndDate(final int activityId){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final Timestamp ret = getLastDate(activityId,true,st);
			con.close();
			return ret;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static Date getFirstDate(final int activityId){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.first(recordedSessionsTable, sessionStartTimeColumn,activityIdColumn,activityId));
			final Timestamp ret = Timestamp.valueOf(rs.getObject(1).toString());
			con.close();
			return new Timestamp(getLocalMillis(ret));
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static String[] getGroupMembers(final String groupName){
		final LinkedList<String> members = new LinkedList<String>();

		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(SqlWrapper.select(groupTable, SqlWrapper.ROWID, activityNameColumn,groupName)); 
			if (! rs.next()) throw new RuntimeException("No entry for " + groupName+".");
			final int groupId = rs.getInt(SqlWrapper.ROWID.getName());
			rs = st.executeQuery(SqlWrapper.select(activitiesTable, activityNameColumn,groupIdColumn,groupId));
			while (rs.next()) members.add(rs.getString(activityNameColumn.getName()));
			rs.close();
			st.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		return members.toArray(new String[0]);
	}

	public static void addActivity(final String newActivity){
		final String[] newActivities = {newActivity};
		addActivity(newActivities);
	}

	public static void addActivity(final String[] newActivities){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			for (final String activity : newActivities) st.executeUpdate(SqlWrapper.insert(activitiesTable,activity));
			con.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}

	public static String getActivityName(final int activityID){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, activityNameColumn, SqlWrapper.ROWID,activityID));
			if (! rs.next()) throw new RuntimeException("No entry found for "+activityName+".");
			final String ret = rs.getString(activityNameColumn.getName());
			con.close();
			return ret;
		}
		catch (Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static int getActivityID(final String activityName){
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, SqlWrapper.ROWID, activityNameColumn,activityName));
			if (! rs.next()) throw new RuntimeException("No entry found for "+activityName+".");
			final int ret =  rs.getInt(SqlWrapper.ROWID.getName());
			st.close();
			st.getConnection().close();
			return ret;
		}
		catch(Exception e){
			e.printStackTrace();
		}
		throw new RuntimeException();
	}

	public static ResultSet getUnpostedSessions() throws Exception{
		final Column[] whats = {sessionStartTimeColumn,sessionEndTimeColumn,activityIdColumn};	
		final Connection con = SqlWrapper.connectToSQLite();

		return con.createStatement().executeQuery("SELECT " + whats[0].getName() + 
				"," + whats[1].getName() + "," + 
				whats[2].getName()+" FROM " + 
				recordedSessionsTable.getName() 
				+" WHERE " + postIdColumn.getName() 
				+ " IS NULL");
	}

	public static String[] getActivities(){
		final LinkedList<String> activities = new LinkedList<String>();
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			final ResultSet rs = st.executeQuery(SqlWrapper.select(activitiesTable, activityNameColumn));
			while (rs.next()) activities.add(rs.getString(activityNameColumn.getName()));
			con.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}

		return activities.toArray(new String[0]);
	}

	public static void addMaintenanceCost(final int secCost){
		/*	addRecordedSession(MAINTANCE_ACTIVITY_ID);
		try {
			Thread.sleep(1000);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		modifyEndTimeOf(MAINTANCE_ACTIVITY_ID,System.currentTimeMillis()+(Constants.SS_MILLIS*secCost));*/
	}

	public boolean updateTimes(final int postID, final double startTime, final double endTime) {
		final double[] times = {startTime, endTime};
		final Column[] columns = {sessionStartTimeColumn, sessionEndTimeColumn};
		try{
			final Connection con = SqlWrapper.connectToSQLite();
			final Statement st = con.createStatement();
			st.executeUpdate(SqlWrapper.update(recordedSessionsTable, columns, times, postIdColumn, postID));
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return true;
	}


}
